<?php

namespace Lib\Db;

use \Lib\Core\Log as log;

class Mysql extends \Home {

    protected $reconnect = false;

	protected $config = array();

    /**
     * PDO对象
     *
     * @var PDO
     */
    private $pdo;

	private $pdoKey;

    /**
     * PDOStatement
     */
    private $sth;

    private $tableName;

    private $tmpTableName;

    private $select;

    private $where;

    private $preParam = array();

    private $limit;

    private $order;

    private $having;

    private $group;

	private $item;

    /**
     * @var \Lib\Core\Pager
     */
    private $pager;
    private $isSubqueries = false;

    private $join = array();

    private $log;

    public function __construct($item = 'default') {
		$this->item = $item;
        $arr = explode('\\', get_called_class());
        $tableName = array_pop($arr);
        $this->setTable($tableName);
        $this->log = Log::instance();
    }

	public function disconnect() {
		MysqlConnect::instance()->disconnect($this->pdoKey);
	}

	public function config($config) {
		$this->config = $config;
	}

    /**
     * 查询出所有.
     *
     * @param string $sql SQL.
     *
     * @return mixed.
     */
    public function query($sql) {
        $logKey = $this->log->start(__CLASS__, __FUNCTION__, $sql);
        $this->connect();
        try {
            $result = $this->pdo->query($sql, \PDO::FETCH_ASSOC)->fetchAll();
            $this->log->end($logKey);
        } catch (\Exception $e) {
            $this->log->end($logKey);
            $this->throwException($e);
        }
        return $result;
    }

    /**
     * 设置表名.
     *
     * @param string $tableName 表.
     *
     * @return $this
     */
    public function setTable($tableName) {
        $this->tableName = $tableName;
        $this->tmpTableName = $tableName;
        return $this;
    }

    /**
     * 查询字段.
     *
     * @param mixed $filed 查询字段.
     *
     * @return $this
     */
    public function select($filed = '*') {
        $filed = $filed ? $filed : '*';
        $this->select = $filed;
        return $this;
    }

    /**
     * 设置分页
     * @param \Lib\Core\Pager $pager
     * @param bool            $isSubqueries 是否使用子查询统计条数.
     *
     * @return $this
     */
    public function pager(\Lib\Core\Pager $pager, $isSubqueries = false) {
        $this->pager = $pager;
        $this->isSubqueries = $isSubqueries;
        return $this;
    }

    /**
     * 设置查询条数.
     *
     * @param string $limit LIMIT.
     *
     * @return $this
     */
    public function limit($start, $limit = null) {
        $this->limit = $start;
        if ($limit) {
            $this->limit .= ',' . $limit;
        }
        return $this;
    }

    /**
     * 关联查询.
     *
     * @param  string $join 表名.
     * @param  string $on   on.
     *
     * @return $this
     */
    public function innerJoin($join, $on) {
        $this->join[] =  array(1, $join, $on);
        return $this;
    }

    /**
     * 关联查询.
     *
     * @param  string $join 表名.
     * @param  string $on   on.
     *
     * @return $this
     */
    public function leftJoin($join, $on) {
        $this->join[] = array(2, $join, $on);
        return $this;
    }

    public function having($having) {
        $this->having = $having;
        return $this;
    }

    public function group($group) {
        $this->group = $group;
        return $this;
    }

    /**
     * 从哪个表查询.
     *
     * @param string $tableName 表.
     *
     * @return $this
     */
    public function from($tableName) {
        $this->tmpTableName = $tableName;
        return $this;
    }

    /**
     * 查询条件.
     *
     * @param array $condition 传入需要查询的条件 如：array('id >' =>10).
     *
     * @return $this
     */
    public function where($condition) {
        $this->where = $condition;
        return $this;
    }

    /**
     * 设置排序.
     *
     * @param string $order 排序.
     *
     * @return $this
     */
    public function order($order) {
        $this->order = $order;
        return $this;
    }

    /**
     * 得到预处理参数.
     *
     * @return array
     */
    public function getPreParam() {
        return $this->preParam;
    }

    /**
     * 查询全部.
     *
     * @param string $key
     *
     * @return array
     */
    public function findAll($key = null) {
        if ($this->pager) {
            $this->connect();
            $countSql = $this->getCountSql();
            $this->prepare($countSql);
            $preParam = $this->getPreParam();
            $this->sthExecute($preParam, false);
            $r = $this->sthFetch();
            $count = $r ? array_pop($r) : 0;
            $count = (int) $count;
            $this->preParam = array();
            $this->pager->count = $count;
            if (!$count) {
                $this->clear();
                return array();
            }
            if (!(int) $this->pager->pageSize) {
                $this->pager->pageSize = 1;
            }
            $this->pager->pageCount = ceil($count / $this->pager->pageSize);
            if ($this->pager->offset || $this->pager->offset === '0' || $this->pager->offset === 0) {
                $this->pager->offset = (int) $this->pager->offset;
                $this->pager->currentPage = (int) ($this->pager->offset / $this->pager->pageSize) + 1;
                $start = $this->pager->offset;
            } else {
                if ($this->pager->currentPage < 1) {
                    $this->pager->currentPage = 1;
                }
                if ($this->pager->limitMaximumPage && $this->pager->currentPage > $this->pager->pageCount) {
                    $this->pager->currentPage = $this->pager->pageCount;
                }
                $start = ($this->pager->currentPage - 1) * $this->pager->pageSize;
            }
            $this->limit($start, $this->pager->pageSize);
        }
        $this->execSelect();
        $result = $this->sthFetchAll();
        if ($key) {
            $arr = array();
            foreach ($result as $obj) {
                $arr[$obj[$key]] = $obj;
            }
            return $arr;
        }
        return $result ? $result : array();
    }

    /**
     * 获取查询条数.
     *
     * @return integer
     */
    public function count() {
        $this->select('count(*)');
        $count = $this->fetchColumn();
        if (!$count) {
            $count = 0;
        } else {
            $count = (int) $count[0];
        }
        return $count;
    }

    /**
     * 查询一条.
     *
     * @return array
     */
    public function findOne() {
        $this->limit = 1;
        $this->execSelect();
        $result = $this->sthFetch();
        return $result ? $result : array();
    }

    public function replace($data) {
        $sql = $this->getInsertSql($data, true);
        $this->prepare($sql);
        $preParam = $this->getPreParam();
        $this->sthExecute($preParam);
        $id = $this->pdo->lastInsertId();
        return $id;
    }

    public function insert($data) {
        $sql = $this->getInsertSql($data);
        $this->prepare($sql);
        $preParam = $this->getPreParam();
        $this->sthExecute($preParam);
        $id = $this->pdo->lastInsertId();
        return $id;
    }

    public function begin() {
        $this->exec('begin');
    }

    public function rollback() {
        $this->exec('rollback');
    }

    public function commit() {
        $this->exec('commit');
    }

    public function update($data) {
        $sql = $this->getUpdateSql($data);
        $this->prepare($sql);
        $preParam = $this->getPreParam();
        return $this->sthExecute($preParam);
    }

    public function del() {
        $sql = $this->getDelSql();
        $this->prepare($sql);
        $preParam = $this->getPreParam();
        return $this->sthExecute($preParam);
    }

    /**
     * 查询一列.
     *
     * @return array
     */
    public function fetchColumn() {
        $this->execSelect();
        $result = $this->sthFetchAll(\PDO::FETCH_COLUMN);
        return $result ? $result : array();
    }

    public function exec($sql) {
        $this->connect();
        $this->pdo->exec($sql);
    }

    public function lockWriteTable($table) {
        $sql = 'lock table `' . $table . '` write';
        $this->connect();
        $this->pdo->exec($sql);
    }

    public function lockReadTable($table) {
        $sql = 'lock table `' . $table . '` read';
        $this->connect();
        $this->pdo->exec($sql);
    }

    public function unLockTable() {
        $sql = 'unlock tables';
        $this->connect();
        $this->pdo->exec($sql);
    }

    /**
     * PDO预处理SQL.
     *
     * @param string $sql SQL.
     *
     * @return void
     */
    public function prepare($sql) {
        $this->connect();
        try {
            $logKey = $this->log->start(__CLASS__, __FUNCTION__, $sql);
            $this->sth = $this->pdo->prepare($sql);
            $this->log->end($logKey);
        } catch (Exception $e) {
            $this->log->end($logKey);
            $this->throwException($e->getMessage(), (int) $e->getCode(), $e);
        }
    }

    /**
     * 预处理后执行数据.
     *
     * @param array   $array 执行预处理后的数据.
     * @param boolean $clear 是否清楚条件.
     *
     * @return void|ini
     */
    public function sthExecute($array = array(), $clear = true) {
        if ($clear) {
            $this->clear();
        }
        try {
            $logKey = $this->log->start(__CLASS__, __FUNCTION__, $array);
            $this->sth->execute($array);
            $num = $this->sth->rowCount();
            $this->log->end($logKey);
            return $num;
        } catch (\Exception $e) {
            $this->log->end($logKey);
            $this->throwException($e->getMessage(), (int) $e->getCode(), $e);
        }
    }

    /**
     * 得到count Sql.
     *
     * @return string
     */
    private function getCountSql() {
        if (!$this->isSubqueries) {
            $countSql = $this->getSelectSql(true);
        } else {
            $sql = $this->getSelectSql();
            $countSql = 'select count(1) from (' . $sql. ') as tb';
        }
        return $countSql;
    }

    private function getTableName() {
        $table = $this->tableName;
        if ($this->tmpTableName) {
            $table = $this->tmpTableName;
        }
        $table = trim($table);
        $table = trim($table, '`');
        if (strpos($table, ' ')) {
            if (stripos($table, 'as')) {
                $table = str_ireplace('as', '', $table);
            }
            $tmp = explode(' ', $table);
            $tmp = array_filter($tmp);
            if (count($tmp) != 2) {
                $this->throwException('set table name error.');
            }
            $tmp = array_values($tmp);
            $returnTable = '`' . $tmp[0] . '` as `' . $tmp[1] . '`';
        } else {
            $returnTable = '`' . $table . '`';
        }
        return $returnTable;
    }

    private function getSelectSql($count = false) {
        if (!$this->select || $this->select == '*') {
            $s = '*';
        } else {
            $s = $this->select;
        }
        if ($count) {
            $s = 'count(*)';
        }
        $table = $this->getTableName();
        $whereSql = $this->getWhereSql();
        $sql = 'select ' . $s . ' from ' . $table;
        if ($this->join) {
            foreach ($this->join as $val) {
                if ($val[0] == 1) {
                    $type = 'inner join';
                } else {
                    $type = 'left join';
                }
                $sql .= ' ' . $type . ' ' . $val[1] . ' on ' . $val[2];
            }
        }
        if ($whereSql) {
            $sql .= ' where ' . $whereSql;
        }
        if ($this->group) {
            $sql .= ' group by ' . $this->group;
        }
        if ($this->having) {
            $sql .= ' having ' . $this->getWhereSql($this->having);
        }
        if ($this->order) {
            $sql .= ' order by ' . $this->order;
        }
        if ($this->limit) {
            $sql .= ' limit ' . $this->limit;
        }
        return $sql;
    }

    private function getInsertSql($data, $replace = false) {
        $sql = 'insert into ';
        if ($replace) {
            $sql = 'replace into ';
        }
        $sql .= $this->getTableName();
        $keys = array_keys($data);
		$keys = array_map(function($k) {
			$k = trim($k, '`');
			$k = '`' . $k . '`';
			return $k;
		}, $keys);
        $r = array();
        $sql .= ' (' . implode(',', $keys). ') values (' . implode(',', array_pad($r, count($data), '?')) . ')';
        $this->preParam = array_values($data);
        return $sql;
    }

    private function getUpdateSql($data) {
        $sql = 'update ' . $this->getTableName() . ' set';
        foreach ($data as $key => $val) {
            if (strpos($key, '`') !== false) {
                $sql .= ' ' . $key;
            } else {
                $sql .= ' `' . $key . '`';
            }
            if (strpos($val, '`') !== false) {
                $sql .= ' = ' . $val . ',';
            } else {
                $sql .= ' = ?,';
                $this->preParam[] = $val;
            }
        }
        $sql = substr($sql, 0, -1);
        $where = $this->getWhereSql();
        if ($where) {
            $sql .= ' where ' . $where;
        }
        return $sql;
    }

    private function getDelSql() {
        $sql = 'delete from ' . $this->getTableName();
        $where = $this->getWhereSql();
        $sql .= ' where ' . $where;
        return $sql;
    }

    private function getWhereSql($where = null) {
        $where = $where ? $where : $this->where;
        if (!$where) {
            return '';
        }
        if (!is_array($where)) {
            $this->throwException('bad where sql.');
        }
        $sql = '';
        foreach ($where as $key => $val) {
            if (!is_array($val)) {
                $this->preParam[] = $val;
            }
            $arr = explode(' ', $key);
            $arr = array_filter($arr);
            if (count($arr) == 1) {
                if (strtolower($key) == 'or') {
                    if (!is_array($val)) {
                        $this->throwException('bad where sql.');
                    }
                    $tmp = array();
                    foreach ($val as $o) {
                        $tmp[] = $this->getWhereSql($o);
                    }
                    $sql .= '(' . implode(' or ', $tmp) . ') and ';
                    continue;
                } else {
                    $arr[] = '=';
                }
            }
            if (count($arr) != 2 && count($arr) != 3) {
                $this->throwException('bad where sql.');
            }
            if (strtolower($arr[1]) == 'in') {
                if (!is_array($val)) {
                    $this->throwException('bad where sql.');
                }
                $r = array_pad(array(), count($val), '?');
                $sql .=  $arr[0] . ' in (' . implode(',', $r) . ') and ';
                $this->preParam = array_merge($this->preParam, $val);
            } else if (strtolower($arr[1]) == 'not' && !empty($arr[2]) && strtolower($arr[2]) == 'in') {
				if (!is_array($val)) {
					$this->throwException('bad where sql.');
				}
				$r = array_pad(array(), count($val), '?');
				$sql .=  $arr[0] . ' not in (' . implode(',', $r) . ') and ';
				$this->preParam = array_merge($this->preParam, $val);
			} else {
			    if (strpos($arr[0], '`') === false) {
                    $arr[0] = '`' . $arr[0] . '`';
                }
                $sql .=  $arr[0] . ' ' . $arr[1] . ' ? and ';
            }
        }
        $sql = '(' . substr($sql, 0, -5) . ')';
        return $sql;
    }

    private function sthFetchAll($type = \PDO::FETCH_ASSOC) {
        try {
            $logKey = $this->log->start(__CLASS__, __FUNCTION__);
            $result = $this->sth->fetchAll($type);
            $result = $result ? $result : array();
            return $result;
        } catch(\PDOException $e) {
            $this->log->end($logKey);
            $this->throwException($e->getMessage(), (int) $e->getCode(), $e);
        }
    }

    private function sthFetch($type = \PDO::FETCH_ASSOC) {
        try {
            $logKey = $this->log->start(__CLASS__, __FUNCTION__);
            $result = $this->sth->fetch($type);
            $result = $result ? $result : array();
            $this->log->end($logKey);
            return $result;
        } catch(\PDOException $e) {
            $this->log->end($logKey);
            $this->throwException($e->getMessage(), (int) $e->getCode(), $e);
        }
    }

    private function execSelect() {
        $this->connect();
        $sql = $this->getSelectSql();
        $this->prepare($sql);
        $preParam = $this->getPreParam();
        $this->sthExecute($preParam);
    }

    private function connect() {
		$item = $this->item;
		if ($this->config) {
			$config = $this->config;
		} else {
            $configClassName = \Main::$appNamespace . 'Config\Mysql';
			$config = new $configClassName();
			$config = $config->$item;
		}
        if ($this->reconnect) {
			MysqlConnect::instance()->disconnect($this->pdoKey);
        }
        if ($this->pdo) {
            return $this;
        } else {
            try {
                $logKey = $this->log->start(__CLASS__, __FUNCTION__, 'connect mysql');
                $this->pdo = MysqlConnect::instance()->connect($config['dsn'], $config['user'], $config['password'], $config['options'], $key);
				$this->pdoKey = $key;
                $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
                $this->log->end($logKey);
            } catch (\PDOException $e) {
                $this->log->end($logKey);
                $this->throwException($e->getMessage(), (int) $e->getCode(), $e);
            }
        }
        return $this;
    }

    private function clear() {
        $this->tmpTableName = $this->tableName;
        $this->select = null;
        $this->where = null;
        $this->preParam = array();
        $this->limit = null;
        $this->order = null;
        $this->join = array();
        $this->having = array();
        $this->group = null;
        $this->pager = null;
        $this->isSubqueries = false;
    }

    private function throwException($msg, $code = 0, $previous = null) {
        throw new \Lib\Exception\Db($msg, $code, $previous);
    }
}